# coding: utf-8

# -------------------------------------------------------------------------------
# Name:         excel_util.py
# Description:  excel解析工具
# Author:       XiangjunZhao
# EMAIL:        2419352654@qq.com
# Date:         2020/7/31 14:27
# -------------------------------------------------------------------------------
import logging
import os

import xlrd
import openpyxl
from openpyxl.styles import PatternFill
from xlrd import XLRDError

logger = logging.getLogger(__name__)


class ExcelReadUtil(object):
    """
    Excel读取数据工具类
    """

    def __init__(self, file_path=None, file_contents=None, sheet_name=None):
        """
        Excel读取数据工具类初始化方法
        Args:
            file_path: 文件路径
            file_contents: 文件内容
            sheet_name: sheet页名称
        """
        self.file_path = file_path
        self.file_contents = file_contents
        self.sheet_name = sheet_name
        self.workbook = None
        self.sheet = None
        try:
            self.workbook = xlrd.open_workbook(filename=self.file_path, file_contents=file_contents)
        except FileNotFoundError as e:
            logger.warning('Excel文件解析失败，原因：{}'.format(str(e)))
        except TypeError as e:
            logger.warning('Excel文件解析失败，原因：{}'.format(str(e)))

        if self.sheet_name:
            try:
                self.sheet = self.workbook.sheet_by_name(sheet_name=self.sheet_name)
            except XLRDError as e:
                logger.warning('Excel文件Sheet页读取失败，原因：{}'.format(str(e)))
        else:
            self.sheet = self.workbook.sheet_by_index(sheetx=0)

    def get_sheets(self):
        """
        获取excel的所有sheet
        Returns:

        """
        return self.workbook.sheets()

    def get_sheet_names(self):
        """
        获取excel所有sheet的名称
        Returns:

        """
        return self.workbook.sheet_names()

    def get_sheet_name(self, sheet=None):
        """
        获取sheet名称
        Args:
            sheet:

        Returns:

        """
        return sheet.name if sheet else self.sheet.name

    def get_nrows(self, sheet=None):
        """
        获取Sheet页总行数
        Args:
            sheet:

        Returns:

        """
        return sheet.nrows if sheet else self.sheet.nrows

    def get_ncols(self):
        """
        获取Sheet页总列数
        Returns:

        """
        return self.sheet.ncols

    def get_title(self, nrow=0):
        """
        读取Excel表头
        Args:
            nrow: 表头行，默认第一行为表头信息

        Returns: 数据列表

        """
        try:
            data = [value for value in self.sheet.row_values(nrow)]
            return data
        except Exception as e:
            logger.warning('读取Excel数据失败，原因：{}'.format(str(e)))

    def get_datas(self, start_row=None, end_row=None, start_col=None, end_col=None):
        """
        读取Excel数据，存放在一个二维列表中返回
        Args:
            start_row: 起始行
            end_row: 结束行
            start_col: 起始列
            end_col: 线束列

        Returns: 数据列表

        """
        start_row = start_row or 0
        end_row = end_row or self.get_nrows()
        start_col = start_col or 0
        end_col = end_col or self.get_ncols()
        try:
            datas = list()
            for nrow in range(start_row, end_row):
                data = [value for value in self.sheet.row_values(nrow)]
                datas.append(data[start_col:end_col])
            return datas
        except Exception as e:
            logger.warning('读取Excel数据失败，原因：{}'.format(str(e)))

    def get_dict_fmt_datas(self, start=None, stop=None):
        """
        获取Sheet页数据，并将数据转换成dict，第一行数据为key
        Args:
            start: 起始行
            stop: 终止行

        Returns:

        """
        keys = self.get_title()
        values = self.get_datas(start, stop)
        return [dict(zip(keys, value)) for value in values]

    def get_dict_fmt_datas_with_subobject(self):
        """
        获取Sheet页数据，并将数据转换成dict，数据含有1个子对象
        Returns: 数据列表

        """
        datas = list()
        parent_ncol = 0
        parent_key = ''
        for ncol in range(self.get_ncols()):
            # 第2行的单元格不为空时，就是对应第1行当前列的子对象；
            # 此循环是为了查询出父对象的键
            if self.sheet.cell_value(1, ncol):
                parent_key = self.sheet.cell_value(0, ncol)
                break
            parent_ncol += 1
            continue

        data = dict()
        data[parent_key] = list()
        for nrow in range(2, self.get_nrows()):
            # 从第3行开始读取数据
            if not self.sheet.cell_value(nrow, 0):
                # 读取子对象数据内容
                child_data = dict()
                for ncol in range(parent_ncol, self.get_ncols()):
                    child_data[self.sheet.cell_value(1, ncol)] = self.sheet.cell_value(nrow, ncol)
                data[parent_key].append(child_data)
            else:
                if data and data[parent_key]:
                    datas.append(data)
                    data = dict()
                    data[parent_key] = list()

                child_data = dict()
                for ncol in range(0, self.get_ncols()):
                    if ncol >= parent_ncol:
                        child_data[self.sheet.cell_value(1, ncol)] = self.sheet.cell_value(nrow, ncol)
                    else:
                        data[self.sheet.cell_value(0, ncol)] = self.sheet.cell_value(nrow, ncol)
                data[parent_key].append(child_data)

            if nrow + 1 == self.get_nrows():
                # 当前行是最后一行时，将读取出来的数据追加到datas中
                if data and data[parent_key]:
                    datas.append(data)
        return datas

    def get_datas_ignore_ncols(self, ncols):
        """
        读取Excel数据，忽略指定列
        Args:
            ncols: 需要忽略的列，类型为列表

        Returns: 数据列表

        """
        try:
            datas = list()
            for nrow in range(self.get_nrows()):
                data = list()
                for ncol in range(self.get_ncols()):
                    if ncol not in ncols:
                        data.append(self.sheet.cell_value(rowx=nrow, colx=ncol))
                data.append(data)
            return datas
        except Exception as e:
            logger.warning('读取Excel数据失败，原因：{}'.format(str(e)))

    def get_datas_ignore_nrows(self, nrows):
        """
        读取Excel数据，忽略指定行
        Args:
            nrows: 需要忽略的行，类型为列表

        Returns: 数据列表

        """
        try:
            datas = list()
            for nrow in range(self.get_nrows()):
                if nrow not in nrows:
                    data = [value for value in self.sheet.row_values(nrow)]
                    datas.append(data)
            return datas
        except Exception as e:
            logger.warning('读取Excel数据失败，原因：{}'.format(str(e)))

    def get_data_by_nrow(self, nrow):
        """
        读取Excel指定行数据
        Args:
            nrow: 需要读取数据的行

        Returns: 数据列表

        """
        try:
            data = [value for value in self.sheet.row_values(nrow)]
            return data
        except Exception as e:
            logger.warning('读取Excel数据失败，原因：{}'.format(str(e)))

    def get_data_by_ncol(self, ncol):
        """
        读取Excel指定列数据
        Args:
            ncol: 需要读取数据的列

        Returns: 数据列表

        """
        try:
            data = [value for value in self.sheet.col_values(ncol)]
            return data
        except Exception as e:
            logger.warning('读取Excel数据失败，原因：{}'.format(str(e)))

    def get_data_by_cell(self, cell):
        """
        读取Excel指定单元格数据
        Args:
            cell: 需要读取数据的单元格，格式为元组，示例：(1,3)

        Returns:

        """
        try:
            data = self.sheet.cell_value(**cell)
            return data
        except Exception as e:
            logger.warning('读取Excel数据失败，原因：{}'.format(str(e)))


class ExcelWriteUtil(object):
    """
    Excel写入数据工具类
    """

    def __init__(self, file_path=None, sheet_name=None):
        """
        Excel写入数据工具类初始化方法
        Args:
            file_path: 文件路径
            sheet_name: sheet页名称
        """
        self.file_path = file_path
        self.sheet_name = sheet_name
        self.workbook = None
        self.sheet = None
        if os.path.exists(file_path) and os.path.isfile(file_path):
            self.workbook = openpyxl.load_workbook(filename=self.file_path)
            if self.sheet_name:
                try:
                    self.sheet = self.workbook[self.sheet_name]
                except KeyError as e:
                    logger.warning('Excel文件Sheet页加载失败，原因：{}'.format(str(e)))
        else:
            self.workbook = openpyxl.Workbook()

        if not self.sheet:
            self.sheet = self.workbook.active

    def create_sheet(self, title, index=0):
        """
        创建sheet页
        Args:
            title:
            index:

        Returns:

        """
        self.sheet = self.workbook.create_sheet(title, index)

    def remove_sheet_by_name(self, name):
        """
        根据sheet名称删除
        Args:
            name:

        Returns:

        """
        self.workbook.remove_sheet(self.workbook.get_sheet_by_name(name))

    def remove_all_sheet(self):
        """
        删除所有sheet页
        Returns:

        """
        for name in self.workbook.get_sheet_names():
            self.workbook.remove_sheet(self.workbook.get_sheet_by_name(name))

    def append_data_by_row(self, datas):
        """
        按行追加内容
        Args:
            datas:

        Returns:

        """
        self.sheet.append(iterable=datas)

    def update_data_by_cell(self, row, column, value):
        """
        修改单元格数据
        Args:


        Returns:

        """
        self.sheet.cell(row, column, value)

    def write_data_by_cell(self, row, column, value):
        """
        单元格写入数据
        Args:
            row: 行
            column: 列
            value: 数据

        Returns:

        """
        self.update_data_by_cell(row, column, value)

    def fill_cell_color(self, row, col, color):
        """
        单元格填充背景色
        Args:
            row: 行
            col: 列
            color: 背景色

        Returns:

        """
        pattern_fill = PatternFill("solid", color)
        self.sheet.cell(row, col).fill = pattern_fill

    def save(self, filename=None):
        """
        保存文件
        Args:
            filename: 文件名称

        Returns:

        """
        filename = filename if filename else self.file_path
        self.workbook.save(filename=filename)


if __name__ == '__main__':
    base_dir = os.path.dirname(os.path.dirname(os.path.abspath(__name__)))
    file_path = os.path.join(os.path.join(base_dir, 'datas'), 'get_data_dict示例文档.xlsx')

    # datas = ExcelReadUtil(file_path=file_path).get_dict_fmt_datas()
    # print(datas)

    excel_write_util = ExcelWriteUtil(file_path=file_path)
    # datas = [[1, 2, 3], [4, 5, 6]]
    # for data in datas:
    #     excel_write_util.append_data_by_row(data)
    # excel_write_util.write_data_by_cell(3, 3, 9)
    # excel_write_util.save()
    excel_write_util.remove_sheet_by_name('Sheet2')

